package cn.cloudreal.cbms.contract.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;

import org.apache.commons.io.FileUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;

import cn.cloudreal.cbms.common.util.Constants;
import cn.cloudreal.cbms.common.util.DatadicContext;
import cn.cloudreal.cbms.common.util.StringUtils;
import cn.cloudreal.cbms.contract.service.ContractListService;
import cn.cloudreal.cbms.system.service.OrganizaService;
import cn.cloudreal.cbms.system.service.UserInfoService;
import cn.cloudreal.cbms.vo.Contract;
import cn.cloudreal.cbms.vo.Dictionary;
import cn.cloudreal.cbms.vo.Organiza;
import cn.cloudreal.cbms.vo.UserInfo;

import com.opensymphony.xwork2.ActionSupport;

/**
 * <p>
 * Title: CBM
 * </p>
 * <p>
 * Description:合同批量导入
 * </p>
 * <p>
 * Copyright: Copyright (c) 2012
 * </p>
 * <p>
 * Company:云恒瑞通
 * </p>
 * 
 * @author liupanpan
 * @date 2012-08-14
 */
public class UploadContractAction extends ActionSupport {

	private ContractListService contractListService = null;
	private UserInfoService userInfoService = null; // 用户表
	private OrganizaService organizaService = null;

	private File uploadFile = null;// 合同文件
	private String uploadFileFileName;
	private String uploadFileContentType;
	private String uploadErrorMessage = "";// 错误信息
	private List<Contract> cList = null;
	private List<Dictionary> contractPaymentList = null;// 付款方式
	private List<Dictionary> cityList = null; // 归属地市
	private List<Organiza> organizaLists = null; // 组织架构
	private List<UserInfo> userList = null; // 签约人List
	private String noError = "";

	/**
	 * 导入合同
	 * 
	 * @return
	 */
	public String doUploadContract() {
		String fliePath = "";// 上传文件路径
		String realpath = ServletActionContext.getServletContext().getRealPath(
				"/upload"); // 获得web应用的真实路径
		String fileName = "HeTong_" + StringUtils.getMathAndTime() + ".xlsx";
		File savefile = new File(realpath, fileName); // 参数1：保存路径，参数2：文件名
		try {
			FileUtils.copyFile(uploadFile, savefile);
		} catch (IOException e) {
			e.printStackTrace();
			return ERROR;
		}
		fliePath = realpath + "/" + fileName;

		// 解析excel
		Workbook workbook = null;
		try {
			workbook = this.create(new FileInputStream(fliePath));
		} catch (Exception e) {
			e.printStackTrace();
			this.deleteFile(fliePath);
			return ERROR;
		}

		try {
			contractPaymentList = DatadicContext.getInstance()
					.getDicDataListByCode("FKFS");// 付款方式
			List<Dictionary> dataListHTLX = DatadicContext.getInstance()
					.getDicDataListByCode("HTLX");// 合同类型
			List<Dictionary> dataListQYJB = DatadicContext.getInstance()
					.getDicDataListByCode("QYJB");// 签约级别
			List<Dictionary> dataListGYSLX = DatadicContext.getInstance()
					.getDicDataListByCode("GYSLX"); // 供应商类型
			// cityList = DatadicContext.getInstance().getSubDatadicByCode("-1",
			// "CITY");// 归属地市
			// 获取当前session对象中所属城市
			UserInfo userInfo = (UserInfo) ServletActionContext.getRequest()
					.getSession().getAttribute(Constants.SESSIONPRKEY);
			String cityKey = userInfo.getUsercity();
			String cityValue = userInfo.getCityName();
			for (int k = 0; k < workbook.getNumberOfSheets(); k++) {
				Sheet sheet = workbook.getSheetAt(k);
				String sheetName = sheet.getSheetName(); // sheet名字
				int rows = sheet.getPhysicalNumberOfRows();
				for (int r = 1; r < rows; r++) {
					Row row = (Row) sheet.getRow(r);
					if (row == null) {
						break;
					}
					if (row != null) {
						boolean bool = true;// 提示
						Contract contract = new Contract();// 合同
						contract.setContractID(getValue(row.getCell(0)).trim());// 合同编号
						contract.setContractName(getValue(row.getCell(1))
								.trim());// 合同名称
						contract.setContractCity(cityKey);// 城市归属
						if ("".equals(contract.getContractID())
								|| StringUtils.getStringLength(contract
										.getContractID()) > 30) {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，合同编号不能为空或长度不能大于30字符！<br/>";
							bool = false;
						}

						if (StringUtils.isChinese(contract.getContractID())) {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，合同编号不能含有中文！<br/>";
							bool = false;
						}

						if (this.StringFilter_Bool(contract.getContractID())) {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，合同编号含有特殊字符！<br/>";
							bool = false;
							continue;
						}

						cList = contractListService.isEixstContractID(contract
								.getContractID(), null);
						if (cList != null && cList.size() != 0) {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，合同编号重复！<br/>";
							bool = false;
							continue;
						}

						if ("".equals(contract.getContractName())
								|| StringUtils.getStringLength(contract
										.getContractName()) > 160) {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，合同名称不能为空或长度不能大于80汉字(160字符)！<br/>";
							bool = false;
						}

						if (StringUtils.StringFilter_Bool(contract
								.getContractName())) {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，合同名称不能含有特殊字符！<br/>";
							bool = false;
							continue;
						}

						String contractType = "";// 合同类型
						for (int i = 0; i < dataListHTLX.size(); i++) {
							if (this.getValue(row.getCell(2)).trim().equals(
									dataListHTLX.get(i).getDatavalue())) {
								contractType = dataListHTLX.get(i).getId() + "";
								break;
							}
						}
						if (!"".equals(contractType)) {
							contract.setContractType(contractType);
						} else {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，合同类型不能为空或格式错误！<br/>";
							bool = false;
						}

						String contractBType = "";// 供应商类型
						for (int i = 0; i < dataListGYSLX.size(); i++) {
							if (this.getValue(row.getCell(3)).trim().equals(
									dataListGYSLX.get(i).getDatavalue())) {
								contractBType = dataListGYSLX.get(i).getId()
										+ "";
								break;
							}
						}
						if (!"".equals(contractBType)) {
							contract.setContractBType(contractBType);
						} else {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，供应商类型不能为空或格式错误！<br/>";
							bool = false;
						}
						contract.setContractBName(this.getValue(row.getCell(4))
								.trim());// 供应商名称
						if ("".equals(contract.getContractBName())
								|| StringUtils.getStringLength(contract
										.getContractBName()) > 160) {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，供应商名称不能为空或长度不能大于80汉字(160字符)！<br/>";
							bool = false;
						}

						if (StringUtils.StringFilter_Bool(contract
								.getContractBName())) {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，供应商名称不能含有特殊字符！<br/>";
							bool = false;
						}

						String contractLevel = "";// 签约级别
						for (int i = 0; i < dataListQYJB.size(); i++) {
							if (this.getValue(row.getCell(5)).trim().equals(
									dataListQYJB.get(i).getDatavalue())) {
								contractLevel = dataListQYJB.get(i).getId()
										+ "";
								break;
							}
						}

						if (!"".equals(contractLevel)) {
							contract.setContractLevel(contractLevel);
						} else {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，签约级别不能为空或格式错误！<br/>";
							bool = false;
						}

						String contractYear = this.getValue(row.getCell(6))
								.trim();// 年度
						if (!"".equals(contractYear)
								&& isFloathString(contractYear)) {
							contract.setContractYear(contractYear);
							if (Integer.parseInt(this.getYear())
									- Integer.parseInt(contractYear) > 0) {
								uploadErrorMessage += sheetName + ",第"
										+ (r + 1) + "行，年度不能小于当年！<br/>";
								bool = false;
							}
						} else {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，年度不能为空或格式错误！<br/>";
							bool = false;
						}
						contract.setContractID(null);// 合同编号
						cList = contractListService
								.findContractByYears(contract);
						if (cList != null && cList.size() != 0) {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，合同名称重复！<br/>";
							bool = false;
							continue;
						}

						contract.setContractID(getValue(row.getCell(0)).trim());// 合同编号
						if (isValidDate(getValue(row.getCell(7)).trim())
								&& !"".equals(getValue(row.getCell(7)).trim())) {
							contract.setContractStartTime(getValue(
									row.getCell(7)).trim());// 开始时间
						} else {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，开始时间不能为空或者格式不正确！<br/>";
							bool = false;
						}

						if (isValidDate(getValue(row.getCell(8)).trim())
								&& !"".equals(getValue(row.getCell(8)).trim())) {
							contract
									.setContractEndTime(getValue(row.getCell(8))
											.trim());// 结束时间
						} else {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，结束时间不能为空或者格式不正确！<br/>";
							bool = false;
						}

						// System.out.println(getValue(row.getCell(7)).trim()+"__"+getValue(row.getCell(8)).trim());
						if (isValidDate(getValue(row.getCell(7)).trim())
								&& isValidDate(getValue(row.getCell(8)).trim())) {
							SimpleDateFormat sdf = new SimpleDateFormat(
									"yyyyMMdd");
							Date date = new Date();
							String nowDate = sdf.format(date);
							String num1 = getValue(row.getCell(7)).trim()
									.substring(0, 4)
									+ getValue(row.getCell(7)).trim()
											.substring(5, 7)
									+ getValue(row.getCell(7)).trim()
											.substring(8);
							String num2 = getValue(row.getCell(8)).trim()
									.substring(0, 4)
									+ getValue(row.getCell(8)).trim()
											.substring(5, 7)
									+ getValue(row.getCell(8)).trim()
											.substring(8);
							if (Integer.parseInt(num2) - Integer.parseInt(num1) < 0) {
								uploadErrorMessage += sheetName + ",第"
										+ (r + 1) + "行，结束时间不能小于开始时间！<br/>";
								bool = false;
							}
							System.out.println(nowDate + "_" + num1 + "_"
									+ num2);
							if (Integer.parseInt(nowDate)
									- Integer.parseInt(num2) > 0) {
								uploadErrorMessage += sheetName + ",第"
										+ (r + 1) + "行，结束时间必须不能大于当前时间！<br/>";
								bool = false;
							}

						}

						String contractPayment = "";// 付款方式
						if (contractPaymentList != null
								&& contractPaymentList.size() != 0) {
							for (int i = 0; i < contractPaymentList.size(); i++) {
								if (this.getValue(row.getCell(9)).trim()
										.equals(
												contractPaymentList.get(i)
														.getDatavalue().trim())) {
									contractPayment = contractPaymentList
											.get(i).getId()
											+ "";
									break;
								}
							}
						}
						if (!"".equals(contractPayment)
								&& contractPayment != null) {
							contract.setContractPayment(contractPayment);// 付款方式
						} else {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，付款方式不能为空或格式错误！<br/>";
							bool = false;
						}

						organizaLists = organizaService
								.getOrganizaByCity(cityKey);
						String contractDepartment = "";// 部门科室

						if (StringUtils.StringFilter_Bool(contractDepartment)) {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，部门科室不能不能含有特殊字符！<br/>";
							bool = false;
							continue;
						}

						if (organizaLists != null && organizaLists.size() != 0) {
							for (int i = 0; i < organizaLists.size(); i++) {
								if (this.getValue(row.getCell(10)).trim()
										.equals(
												organizaLists.get(i)
														.getOrgName().trim())) {
									contractDepartment = organizaLists.get(i)
											.getOrgCode();
									break;
								}
							}
						}
						if (!"".equals(contractDepartment)
								&& contractDepartment != null) {
							contract.setContractDepartment(contractDepartment);// 部门科室
						} else {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，部门科室不能为空或部门科室不存在！<br/>";
							bool = false;
						}

						System.out.println(contractDepartment);
						userList = userInfoService
								.getUserInfoByOrgCode(contractDepartment);
						String contractOffice = "";// 签约人
						int contractor = 0;// 签约人ID
						if (userList != null && userList.size() != 0) {
							for (int i = 0; i < userList.size(); i++) {
								if (this.getValue(row.getCell(11)).trim()
										.equals(userList.get(i).getUserName())) {
									contractOffice = userList.get(i)
											.getUserName();// 签约人名称
									contractor = userList.get(i).getId();// 签约人ID
									break;
								}
							}
						}
						if (!"".equals(contractOffice)
								&& contractOffice != null && contractor != 0) {
							contract.setContractOffice(contractOffice);// 签约人
							contract.setContractor(String.valueOf(contractor));// 签约人ID
						} else {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，签约人不能为空或部门科室下无此签约人！<br/>";
							bool = false;
						}

						contract.setContractDescription(this.getValue(
								row.getCell(12)).trim());// 描述
						if (StringUtils.getStringLength(contract
								.getContractDescription()) > 600) {
							uploadErrorMessage += sheetName + ",第" + (r + 1)
									+ "行，合同描述长度不能大于300汉字(600字符)！<br/>";
							bool = false;
						}

						if (bool) {
							contractListService.addContract(contract);// 添加合同
							this.deleteFile(fliePath);// 删除文件
						}
					}
				}
			}
			if ("".equals(uploadErrorMessage)) {
				noError = "noError";
			}

		} catch (Exception e) {
			e.printStackTrace();
			this.deleteFile(fliePath);// 删除文件
			return ERROR;
		}
		return SUCCESS;
	}

	/**
	 * 判断是否日期
	 * 
	 * @param s
	 * @return
	 */
	private boolean isValidDate(String s) {
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		dateFormat.setLenient(false);
		try {
			dateFormat.parse(s);
			return true;
		} catch (Exception e) {
			// 如果throw java.text.ParseException或者NullPointerException，就说明格式不对
			return false;
		}
	}

	/**
	 * 判断是否整数
	 * 
	 * @param testString
	 * @return
	 */
	private boolean isNumberString(String testString) {
		String numAllString = "0123456789";
		if (testString.length() <= 0)
			return false;
		for (int i = 0; i < testString.length(); i++) {
			String charInString = testString.substring(i, i + 1);
			if (!numAllString.contains(charInString))
				return false;
		}
		return true;
	}

	/**
	 * 判断是否Float
	 * 
	 * @param testString
	 * @return
	 */
	public boolean isFloathString(String testString) {
		if (!testString.contains(".")) {
			return isNumberString(testString);
		} else {
			String[] floatStringPartArray = testString.split("\\.");
			if (floatStringPartArray.length == 2) {
				if (isNumberString(floatStringPartArray[0]) == true
						&& isNumberString(floatStringPartArray[1]) == true)
					return true;
				else
					return false;
			} else
				return false;

		}
	}

	/**
	 * 得到Excel表中的值
	 * 
	 * @param hssfCell
	 *            Excel中的每一个格子
	 * @return Excel中每一个格子中的值
	 */
	public String getValue(Cell hssfCell) {
		if (hssfCell == null) {
			return "";
		}
		if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
			// 返回布尔类型的值
			return String.valueOf(hssfCell.getBooleanCellValue()).trim();
		}
		if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
				SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");
				Date dt = HSSFDateUtil.getJavaDate(hssfCell
						.getNumericCellValue());
				return dateformat.format(dt).trim();
			} else {
				// 返回数值类型的值
				String str = String.valueOf(hssfCell.getNumericCellValue())
						.trim();
				if (isFloathString(str)) {
					double num = Double.parseDouble("0"
							+ str.substring(str.indexOf(".")));
					if (num == 0.0) {
						int num2 = Integer.parseInt(str.substring(0, str
								.indexOf(".")));
						return String.valueOf(num2);
					} else {
						return str;
					}
				}
			}
		}
		// 返回字符串类型的值
		return String.valueOf(hssfCell.getStringCellValue()).trim();
	}

	/**
	 * 返回当前年月日
	 * 
	 * @return
	 */
	public String getYear() {
		java.text.SimpleDateFormat df = new java.text.SimpleDateFormat("yyyy");
		Calendar c = Calendar.getInstance();
		c.set(c.DATE, 1);
		return df.format(c.getTime());
	}

	/**
	 * 删除单个文件
	 * 
	 * @param sPath
	 *            被删除文件的文件名
	 * @return 单个文件删除成功返回true，否则返回false
	 */
	public boolean deleteFile(String sPath) {
		boolean flag = false;
		File file = new File(sPath);
		// 路径为文件且不为空则进行删除
		if (file.isFile() && file.exists()) {
			file.delete();
			flag = true;
		}
		return flag;
	}

	// string 转换date
	public Date getDateFstr(String datestr, String frmtstr) throws Exception {
		Date date = null;
		if (datestr != null && !"".equals(datestr) && frmtstr != null
				&& !"".equals(frmtstr)) {
			SimpleDateFormat sdf = new SimpleDateFormat(frmtstr);
			date = sdf.parse(datestr);
		}
		return date;
	}

	// 判断特殊字符，有的话true 否则没有 false
	public static Boolean StringFilter_Bool(String str)
			throws PatternSyntaxException {
		// 只允许字母和数字
		// String regEx = "[^a-zA-Z0-9]";
		// 清除掉所有特殊字符
		String regEx = "[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]";
		Pattern pattern = Pattern.compile(regEx);
		Matcher matcher = pattern.matcher(str);
		boolean bool = matcher.find();
		return bool;
	}

	private Workbook create(InputStream inp) throws IOException,
			InvalidFormatException {
		if (!inp.markSupported()) {
			inp = new PushbackInputStream(inp, 8);
		}
		if (POIFSFileSystem.hasPOIFSHeader(inp)) {
			return new HSSFWorkbook(inp);
		}
		if (POIXMLDocument.hasOOXMLHeader(inp)) {
			return new XSSFWorkbook(OPCPackage.open(inp));
		}
		throw new IllegalArgumentException("你的excel版本目前poi解析不了");
	}

	public File getUploadFile() {
		return uploadFile;
	}

	public void setUploadFile(File uploadFile) {
		this.uploadFile = uploadFile;
	}

	public String getUploadFileFileName() {
		return uploadFileFileName;
	}

	public void setUploadFileFileName(String uploadFileFileName) {
		this.uploadFileFileName = uploadFileFileName;
	}

	public String getUploadFileContentType() {
		return uploadFileContentType;
	}

	public void setUploadFileContentType(String uploadFileContentType) {
		this.uploadFileContentType = uploadFileContentType;
	}

	public void setContractListService(ContractListService contractListService) {
		this.contractListService = contractListService;
	}

	public void setUserInfoService(UserInfoService userInfoService) {
		this.userInfoService = userInfoService;
	}

	public void setOrganizaService(OrganizaService organizaService) {
		this.organizaService = organizaService;
	}

	public String getUploadErrorMessage() {
		return uploadErrorMessage;
	}

	public void setUploadErrorMessage(String uploadErrorMessage) {
		this.uploadErrorMessage = uploadErrorMessage;
	}

	public String getNoError() {
		return noError;
	}

	public void setNoError(String noError) {
		this.noError = noError;
	}

	public static void main(String[] args) {
		System.out.println(StringUtils.isChinese("heTong合_003"));
		System.out.println(StringUtils.isChinese("adf"));

	}
}
